IF OBJECT_ID('dbo.vwInfoEdMasterAmendments') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwInfoEdMasterAmendments
    IF OBJECT_ID('dbo.vwInfoEdMasterAmendments') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwInfoEdMasterAmendments >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwInfoEdMasterAmendments >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwInfoEdMasterAmendments
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 04/09/2008
--
-- Description: View to get Master and Amendments "first" status date
--   going CSCR Executed or CSCR Pending Signature
--
-- Uses: View, dbo.vwInfoEdProposals
--
-- Used BY Procedure/Report: A few CSCR Reports
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 04/09/08    KC        Created
-- 04/15/08    KC        Interpolate PendingSigDate when missing, but
--                       ExecutedDate is there.
--
-- =======================================================================
CREATE VIEW dbo.vwInfoEdMasterAmendments
AS
     -- Override some dates
     SELECT    AGR.MasterFolderNumber,
               AGR.prop_no,
               AGR.inst_no,
               AGR.TranType,
               AGR.SubmitDate,
               AGR.CreateDate,
               ISNULL(AGR.PendingDate,AGR.CreateDate) AS PendingDate,
               CASE WHEN AGR.PendingSigDate IS NULL AND AGR.ExecutedDate IS NOT NULL
                    THEN DATEADD(DD,DATEDIFF(DD,ISNULL(AGR.PendingDate,AGR.CreateDate),AGR.ExecutedDate)/2,ISNULL(AGR.PendingDate,AGR.CreateDate))
                    ELSE AGR.PendingSigDate
               END AS PendingSigDate,
               --AGR.PendingSigDate,
               AGR.ExecutedDate
     FROM (
          -- Combine Results into Flat Records
          SELECT  MA.MasterFolderNumber,
                  MA.prop_no,
                  MA.inst_no,
                  MA.TranType,
                  MIN(CASE WHEN MA.PropStatusID = 0 THEN MA.FirstPropStatDate
                      ELSE NULL END) AS SubmitDate,
                  MIN(CASE WHEN MA.PropStatusID = 1 THEN MA.FirstPropStatDate
                      ELSE NULL END) AS CreateDate,
                  MIN(CASE WHEN MA.PropStatusID = 2 THEN MA.FirstPropStatDate
                      ELSE NULL END) AS ExecutedDate,
                  MIN(CASE WHEN MA.PropStatusID = 3 THEN MA.FirstPropStatDate
                  ELSE NULL END) AS PendingSigDate,
                  MIN(CASE WHEN MA.PropStatusID = 4 THEN MA.FirstPropStatDate
                  ELSE NULL END) AS PendingDate
          FROM (
               -- CSCR Execute | CSCR Pending Signature
               SELECT    CASE WHEN CHARINDEX('-',P.inst_no) > 0 
                              THEN SUBSTRING(P.inst_no,1,CHARINDEX('-',P.inst_no) - 1) 
                              ELSE P.inst_no 
                         END AS MasterFolderNumber,
                         P.prop_no,
                         P.inst_no,
                         CASE WHEN P.prop_type = '2' THEN 'Master'
                              WHEN P.prop_type in ('14','20') THEN 'Amendment'
                         END AS TranType,
                         CASE WHEN P.prop_stat IN ('PARTNERS_P_PROP_STAT_66','PARTNERS_PD_PROP_STAT_24','PARTNERS_PD_PROP_STAT_2')
                                   THEN 2                   -- CSCR - Executed
                              WHEN P.prop_stat IN ('PARTNERS_P_PROP_STAT_65')
                                   THEN 3                   -- CSCR - Pending Signature
                              WHEN P.prop_stat IN ('PARTNERS_PD_PROP_STAT_PN')
                                   THEN 4                   -- Pending
                         END AS PropStatusID,                    
                         MIN(P.PropStatDate) AS FirstPropStatDate
               FROM      dbo.vwInfoEdProposals P
               WHERE     P.stat_type = 0 AND
                         P.prop_type in ('2','14','20') AND
                         P.prop_stat in ('PARTNERS_P_PROP_STAT_66','PARTNERS_PD_PROP_STAT_24','PARTNERS_PD_PROP_STAT_2','PARTNERS_P_PROP_STAT_65','PARTNERS_PD_PROP_STAT_PN')
               GROUP BY  P.prop_no, 
                         P.inst_no,
                         CASE WHEN P.prop_type = '2' THEN 'Master'
                              WHEN P.prop_type in ('14','20') THEN 'Amendment'
                         END,
                         CASE WHEN P.prop_stat IN ('PARTNERS_P_PROP_STAT_66','PARTNERS_PD_PROP_STAT_24','PARTNERS_PD_PROP_STAT_2')
                                   THEN 2                   -- CSCR - Executed
                              WHEN P.prop_stat IN ('PARTNERS_P_PROP_STAT_65')
                                   THEN 3                   -- CSCR - Pending Signature
                              WHEN P.prop_stat IN ('PARTNERS_PD_PROP_STAT_PN')
                                   THEN 4                   -- Pending
                         END
               UNION
               -- Submit Date!  
               SELECT    CASE WHEN CHARINDEX('-',P.inst_no) > 0 
                              THEN SUBSTRING(P.inst_no,1,CHARINDEX('-',P.inst_no) - 1) 
                              ELSE P.inst_no 
                         END AS MasterFolderNumber,
                         P.prop_no,
                         P.inst_no,
                         CASE WHEN P.prop_type = '2' THEN 'Master'
                              WHEN P.prop_type in ('14','20') THEN 'Amendment'
                         END AS TranType,
                         0 AS PropStatusID,            -- 'CSCR - Submitted' AS PropStatus,
                         MIN(P.submt_dt) AS FirstPropStatDate
               FROM      dbo.vwInfoEdProposals P
               WHERE     P.stat_type = 0 AND
                         P.prop_type in ('2','14','20') 
               GROUP BY  P.prop_no, 
                         P.inst_no,
                         CASE WHEN P.prop_type = '2' THEN 'Master'
                              WHEN P.prop_type in ('14','20') THEN 'Amendment'
                         END 
               UNION
               -- First Stat Date!  
               SELECT    CASE WHEN CHARINDEX('-',P.inst_no) > 0 
                              THEN SUBSTRING(P.inst_no,1,CHARINDEX('-',P.inst_no) - 1) 
                              ELSE P.inst_no 
                         END AS MasterFolderNumber,
                         P.prop_no,
                         P.inst_no,
                         CASE WHEN P.prop_type = '2' THEN 'Master'
                              WHEN P.prop_type in ('14','20') THEN 'Amendment'
                         END AS TranType,
                         1 AS PropStatusID,                 --   'CSCR - FirstStatDate' AS PropStatus,                    
                         MIN(P.PropStatDate) AS FirstPropStatDate
               FROM      dbo.vwInfoEdProposals P
               WHERE     P.stat_type = 0 AND
                         P.prop_type in ('2','14','20') 
               GROUP BY  P.prop_no, 
                         P.inst_no,
                         CASE WHEN P.prop_type = '2' THEN 'Master'
                              WHEN P.prop_type in ('14','20') THEN 'Amendment'
                         END  ) MA
          GROUP BY  MA.MasterFolderNumber,
                    MA.prop_no,
                    MA.inst_no,
                    MA.TranType    ) AGR                
               
go
IF OBJECT_ID('dbo.vwInfoEdMasterAmendments') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwInfoEdMasterAmendments >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwInfoEdMasterAmendments >>>'
go
